#!/bin/bash
export LANG=zh_CN.UTF-8
HIVE_HOME=/usr/bin/hive
${HIVE_HOME} -S -e

CREATE DATABASE edu_dws;
-- DROP DATABASE edu_dwb;
-- DROP table  dwb_signup_customer_relationship;
USE edu_dws;
--分区
SET hive.exec.dynamic.partition=true;
SET hive.exec.dynamic.partition.mode=nonstrict;
set hive.exec.max.dynamic.partitions.pernode=10000;
set hive.exec.max.dynamic.partitions=100000;
set hive.exec.max.created.files=150000;
--hive压缩
set hive.exec.compress.intermediate=true;
set hive.exec.compress.output=true;
--写入时压缩生效
set hive.exec.orc.compression.strategy=COMPRESSION;

--开启map端join
set hive.auto.convert.join=true;
set hive.auto.convert.join.noconditionaltask.size=512000000;
--todo 由于join的9张表中 比如退款表能够满足小表的条件 符合自动转换的标准
--写入数据强制分桶
set hive.enforce.bucketing=true;
--写入数据强制排序
set hive.enforce.sorting=true;
--开启bucketmapjoin
set hive.optimize.bucketmapjoin = true;
--开启SMB Join
set hive.optimize.bucketmapjoin.sortedmerge=true;
set hive.auto.convert.sortmerge.join=true;
set hive.auto.convert.sortmerge.join.noconditionaltask=true;


DROP TABLE dws_signup_customer_relationship;
--创建报名用户关系表：dwb_signup_customer_relationship
CREATE TABLE dws_signup_customer_relationship
(
    fact_customer_id                INT COMMENT '意向id',
    fact_customer_create_date_time  STRING COMMENT '创建时间',
    deleted                         TINYINT COMMENT '是否被删除（禁用）',
    customer_id                     INT COMMENT '所属客户id',
    origin_type                     STRING COMMENT '线上线下',
    fact_customer_itcast_school_id  INT COMMENT '校区Id',
    fact_customer_itcast_subject_id INT COMMENT '学科Id',
    creator                         INT COMMENT '销售id',
    origin_channel                  STRING COMMENT '来源渠道',
    payment_state                   STRING COMMENT '支付状态',
    payment_time                    STRING COMMENT '支付时间',
    course_id                       INT COMMENT '课程ID',
    course_name                     STRING COMMENT '课程名称',

    dim_itcast_clazz_id             INT COMMENT 'ems班级id(非自增)',
    create_date_time                STRING COMMENT '创建时间',
    update_date_time                STRING COMMENT '最后更新时间',
    itcast_school_id                INT COMMENT 'ems校区ID',
    itcast_school_name              STRING COMMENT 'ems校区名称',
    itcast_subject_id               INT COMMENT 'ems学科ID',
    itcast_subject_name             STRING COMMENT 'ems学科名称',

    dim_employee_id                 INT COMMENT '员工id',
    real_name                       STRING COMMENT '员工的真实姓名',
    tdepart_id                      INT COMMENT '直属部门id',

    dim_scrm_department_id          INT COMMENT '部门id',
    tdepart_name                            STRING COMMENT '部门名称',
    create_year                      string comment '年',
    create_month                    string comment '月'
) COMMENT '报名用户关系表'
    PARTITIONED BY (dt STRING)
    ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
    STORED AS TEXTFILE;


SET hive.support.concurrency=false;
set hive.auto.convert.join=false;
set hive.exec.dynamic.partition.mode=nonstrict;


set hive.auto.convert.join=false;

INSERT INTO dws_signup_customer_relationship partition(dt)
SELECT
       p.id as fact_customer_id   ,
       p.create_date_time as fact_customer_create_date_time,
       p.deleted,
       p.customer_id,
       p.origin_type,
       p.itcast_school_id as fact_customer_itcast_school_id  ,
       p.itcast_subject_id as fact_customer_itcast_subject_id,
       p.creator,
       p.origin_channel,
       p.payment_state,
       p.payment_time,
       p.course_id,
       p.course_name,

       z.id as dim_itcast_clazz_id   ,
       z.create_date_time,
       z.update_date_time,
       z.itcast_school_id,
       z.itcast_school_name,
       z.itcast_subject_id,
       z.itcast_subject_name,

       e.id as dim_employee_id ,
       e.real_name,
       e.tdepart_id,

       t.id as clue_id ,
       t.name as tdepart_name,
       substring(p.create_date_time,1,4) as create_year,
       substring(p.create_date_time,1,7) as create_month ,
      substring(p.create_date_time,1,10) as dt
FROM (select * from edu_dwd.dwd_fact_customer_relationship
where id is not null and deleted<>1  LIMIT 10000 ) p
LEFT JOIN (select * from edu_dwd.dwd_dim_itcast_clazz  LIMIT 10000 )z
                   ON p.itcast_school_id= z.itcast_school_id

                   and end_date='9999-12-31'
LEFT JOIN (select * from edu_dwd.dwd_dim_employee LIMIT 10000 ) e
                   ON p.creator = e.id
LEFT JOIN (select * from edu_dwd.dwd_dim_scrm_department LIMIT 10000 ) t
                   ON e.tdepart_id = t.id;


drop table  dws_signup_customer;
CREATE TABLE dws_signup_customer
(
    fact_customer_id                INT COMMENT '意向id',
    fact_customer_create_date_time  STRING COMMENT '创建时间',
    deleted                         TINYINT COMMENT '是否被删除（禁用）',
    customer_id                     INT COMMENT '所属客户id',
    origin_type                     STRING COMMENT '线上线下',
    fact_customer_itcast_school_id  INT COMMENT '校区Id',
    fact_customer_itcast_subject_id INT COMMENT '学科Id',
    creator                         INT COMMENT '销售id',
    origin_channel                  STRING COMMENT '来源渠道',
    payment_state                   STRING COMMENT '支付状态',
    payment_time                    STRING COMMENT '支付时间',
    course_id                       INT COMMENT '课程ID',
    course_name                     STRING COMMENT '课程名称',

    clue_id                         INT COMMENT '线索id',
    clue_create_time                STRING COMMENT '创建时间',
    dim_update_date_time            STRING COMMENT '最后更新时间',
    dim_customer_id                 INT COMMENT '客户id',
    customer_relationship_id        INT COMMENT '客户关系id',
    dim_itcast_school_id            INT COMMENT '校区Id',
    itcast_school                   STRING COMMENT '校区',
    dim_itcast_subject_id           INT COMMENT '学科Id',
    itcast_subject                  STRING COMMENT '学科',
    level                           STRING COMMENT '客户级别',
    dim_origin_type                 STRING COMMENT '数据来源渠道',
    valid                           TINYINT COMMENT '该线索是否是网资有效线索',
    clue_state                      STRING COMMENT '线索状态',

    customer_relationship_first_id  INT COMMENT '意向id',
    appeal_status                   INT COMMENT '申诉状态，0:待稽核 1:无效 2：有效',


    create_year                      string comment '年',
    create_month                    string comment '月'

) COMMENT '线索用户关系表'
    PARTITIONED BY (dt STRING)
    ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
    STORED AS TEXTFILE;


INSERT INTO dws_signup_customer partition(dt)
SELECT
       p.id as fact_customer_id   ,
       p.create_date_time as fact_customer_create_date_time,
       p.deleted,
       p.customer_id,
       p.origin_type,
       p.itcast_school_id as fact_customer_itcast_school_id  ,
       p.itcast_subject_id as fact_customer_itcast_subject_id,
       p.creator,
       p.origin_channel,
       p.payment_state,
       p.payment_time,
       p.course_id,
       p.course_name,

        cl.id,
        cl.create_date_time,
        cl.update_date_time,
        cl.customer_id,
        cl.customer_relationship_id,
        cl.itcast_school_id,
        cl.itcast_school,
        cl.itcast_subject_id,
        cl.itcast_subject,
        cl.level,
        cl.origin_type,
        cl.valid,
        cl.clue_state,



        app.customer_relationship_first_id  ,
        app.appeal_status,

        substring(p.create_date_time,1,4) as create_year,
       substring(p.create_date_time,1,7) as create_month ,
      substring(p.create_date_time,1,10) as dt
FROM (select * from edu_dwd.dwd_fact_customer_relationship  LIMIT 10000 ) p
LEFT JOIN (select * from edu_dwd.dwd_dim_customer_clue  LIMIT 10000 )cl
                   ON p.id = cl.customer_relationship_id
                   and end_date='9999-12-31'
LEFT JOIN (select * from edu_dwd.dwd_dim_customer_appeal
                    WHERE appeal_status =2
                    LIMIT 10000 )app
                   ON cl.customer_relationship_id= app.customer_relationship_first_id;